package com.medicinemanager.sql;

import java.io.File;

import com.medicinemanager.util.Configure;

import android.content.Context;
import android.content.ContextWrapper;
import android.database.DatabaseErrorHandler;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class SQLManager extends SQLiteOpenHelper {
	final private static int VERSION = 3;
	final private static String NAME = "medicine.db";

	public SQLManager(Context context, String name, int version,
			DatabaseErrorHandler databaseErrorHandler) {
		super(new MyContextWrapper(context), name, null, version,
				databaseErrorHandler);
	}

	public SQLManager(Context context) {
		super(new MyContextWrapper(context), NAME, null, VERSION);
	}

	public void onCreate(SQLiteDatabase db) {
		initDatabase(db);
	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	private void initDatabase(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			// create table
			db.execSQL("CREATE TABLE IF NOT EXISTS staff (id char(14) primary key, account varchar(20), password varchar(20), name varchar(8), sex char(2), age integer, education varchar(20), job varchar(20))");
			db.execSQL("CREATE TABLE IF NOT EXISTS customer (id char(14) primary key, name varchar(8) not null, tel char(11), order_time date, order_medicine_id char(14), order_medicine_name varchar(50), order_number integer)");
			db.execSQL("CREATE TABLE IF NOT EXISTS medicine_information (id char(14) primary key, name varchar(50), factory varchar(50), date_fa date, date_save date, useage varchar(50), price float, number integer, person varchar(10), personid char(14))");
			db.execSQL("CREATE TABLE IF NOT EXISTS storehouse (id integer primary key autoincrement, time DATETIME, medicine_id char(14), medicine_name varchar(50), number integer, position varchar(40), staffid char(14))");

			// create constraint
			/*
			 * db.execSQL(
			 * "ALTER TABLE customer ADD CONSTRAINT fk_customer FOREIGN KEY (order_medicine_id) REFERENCES medicine_information(id)"
			 * ); db.execSQL(
			 * "ALTER TABLE medicine_information ADD CONSTRAINT fk_medicine FOREIGN KEY (personid) REFERENCES staff(id)"
			 * ); db.execSQL(
			 * "ALTER TABLE storehouse ADD CONSTRAINT fk_store FOREIGN KEY (medicine_id) REFERENCES medicine_information(id)"
			 * ); db.execSQL(
			 * "ALTER TABLE storehouse ADD CONSTRAINT fk_store2 FOREIGN KEY (staffid) REFERENCES staff(id)"
			 * );
			 */
			// insert the root user	
			db.setTransactionSuccessful();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}
	}
}

	class MyContextWrapper extends ContextWrapper {

		private String mPath = Configure.getInstance().getPath();

		public MyContextWrapper(Context base, String path) {
			super(base);
			mPath = path;
		}

		public MyContextWrapper(Context base) {
			super(base);
		}

		public File getDatabasePath(String name) {
			File result = new File(mPath + File.separator + name);
			if (!result.getParentFile().exists()) {
				result.getParentFile().mkdirs();
			}
			return result;
		}

		public SQLiteDatabase openOrCreateDatabase(String name, int mode,
				CursorFactory factory) {
			return SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name),
					factory);
		}

		public SQLiteDatabase openOrCreateDatabase(String name, int mode,
				CursorFactory factory, DatabaseErrorHandler errorHandler) {
			return SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name)
					.getAbsolutePath(), factory, errorHandler);
		}
	}
